package com.cyou.nad.datastore.query;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import com.cyou.nad.dao.PojoPage;
import com.cyou.nad.dao.jdbc.ReflectionRowMapper;
import com.cyou.nad.datastore.DataList;
import com.cyou.nad.datastore.query.dialect.Dialect;
import com.cyou.nad.datastore.query.dialect.DialectFactory;


@Service(value="jdbcPageDao")
public class JdbcPageDaoImpl implements JdbcPageDao {

	public static final int DEFAULT_MAX_ROWS = 70000;
	
	private final Log log = LogFactory.getLog(getClass());
	/**
	 * JdbcTemplate maxRows
	 */
	private int maxRows = DEFAULT_MAX_ROWS;
	
	@Autowired(required=false)
	@Qualifier(value="jdbcPageDaoJdbcTemplate")
	private JdbcTemplate jdbcPageDaoJdbcTemplate;

	private DataSource dataSource;
	
	private Map<JdbcTemplate, String> databaseProduct= new HashMap<JdbcTemplate, String>();

	public DataList query(String sql, Object... values) {
		Assert.notNull(jdbcPageDaoJdbcTemplate, "没有默认的jdbcTemplate");
		if (log.isDebugEnabled()) {
			log.debug("sql: " + sql);
			log.debug("values :" + Arrays.toString(values));
		}
		ResultSetExtractor<DataList> rse = new ResultSetExtractor<DataList>() {
			public DataList extractData(ResultSet rs) throws SQLException, DataAccessException {
				return new JdbcPageDataList(rs);
			}
		};
		DataList dataList = jdbcPageDaoJdbcTemplate.query(sql, values, rse);
		return dataList;
	}

	public DataList query(DataSource ds, String sql, Object... values) {
		if (log.isDebugEnabled()) {
			log.debug("sql: " + sql);
			log.debug("values :" + Arrays.toString(values));
		}
		ResultSetExtractor<DataList> rse = new ResultSetExtractor<DataList>() {
			public DataList extractData(ResultSet rs) throws SQLException, DataAccessException {
				return new JdbcPageDataList(rs);
			}
		};
		JdbcTemplate jdbcTemplate = getJdbcTemplateForDS(ds);
		DataList dataList = (jdbcTemplate.query(sql, values, rse));
		return dataList;
	}

	/* (non-Javadoc)
	 * @see com.inspur.kpiengine.data.page.JdbcPageDao#pageQuery(java.lang.String, int, int, java.lang.Object)
	 */

	public JdbcPage pageQuery(final String sql, int pageNo, final int pageSize, final Object... values) {
		Assert.notNull(jdbcPageDaoJdbcTemplate, "没有默认的jdbcTemplate");
		String tSql = new StringBuffer("select count(*) from (").append(removeOrders(sql)).append(") t").toString();
		return this.pageQuery(jdbcPageDaoJdbcTemplate, sql, tSql, pageNo, pageSize, values);
	}

	public JdbcPage pageQuery(final String sql, final String tSql, int pageNo, final int pageSize,
			final Object... values) {
		Assert.notNull(jdbcPageDaoJdbcTemplate, "没有默认的jdbcTemplate");

		return this.pageQuery(jdbcPageDaoJdbcTemplate, sql, tSql, pageNo, pageSize, values);
	}

	public JdbcPage pageQuery(DataSource ds, final String sql, int pageNo, final int pageSize, final Object... values) {
		//select count(1) 比select count(*) 更快些
		String tSql = new StringBuffer("select count(1) from (").append(removeOrders(sql)).append(") a").toString();
		
		JdbcTemplate jdbcTemplate = getJdbcTemplateForDS(ds);
		return this.pageQuery(jdbcTemplate, sql, tSql, pageNo, pageSize, values);
	}

	private JdbcTemplate getJdbcTemplateForDS(DataSource ds) {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
		jdbcTemplate.setMaxRows(maxRows);
		return jdbcTemplate;
	}

	public JdbcPage pageQuery(DataSource ds, final String sql, final String tSql, int pageNo, final int pageSize,
			final Object... values) {
		JdbcTemplate jdbcTemplate = getJdbcTemplateForDS(ds);
		return this.pageQuery(jdbcTemplate, sql, tSql, pageNo, pageSize, values);
	}

	/* (non-Javadoc)
	 * @see com.inspur.kpiengine.data.page.JdbcPageDao#pageQuery(org.springframework.jdbc.core.JdbcTemplate, java.lang.String, java.lang.String, int, int, java.lang.Object)
	 */
	public JdbcPage pageQuery(JdbcTemplate jdbcTemplate, final String sql, final String tSql, int pageNo,
			final int pageSize, final Object... values) {

		if (log.isDebugEnabled()) {
			log.debug("sql: " + sql);
			log.debug("tSql : " + tSql);
			log.debug("pageNo : " + pageNo);
			log.debug("pageSize : " + pageSize);
			log.debug("values :" + Arrays.toString(values));
		}
		Assert.notNull(jdbcTemplate, "需要有连接池");
		Assert.hasText(sql);
		Assert.hasText(tSql);
		Assert.isTrue(pageNo >= 1, "pageNo 应该从1开始");
		Assert.isTrue(pageSize >= 1, "pageSize 应该大于等于1");
		//获取总数
		int totalCount = jdbcTemplate.queryForInt(tSql, values);
		try {
			if(log.isTraceEnabled()){
				log.trace(jdbcTemplate.getDataSource().getConnection().getMetaData().getURL());
			}
		} catch (SQLException e) {
		}
		if (totalCount <= 0)//如果没有数据则返回一个空的jdbcpage
			return new JdbcPage();
		//获取本页第一个数据的index
		final int startIndex = getStartOfPage(pageNo, pageSize);
		//在这里判断适用的数据库类型
		Dialect dialect = DialectFactory.getDialect(jdbcTemplate.getDataSource());
		//根据获取的数据库类型不同，在这里调用不同数据库的方法。
		String sqlLimit = dialect.getLimitString(sql, true);
		//获取一页的开始到和一页大小（为了limit select做准备）
		Object[] limitParam = dialect.getLimitParam(pageNo,pageSize, true);
		if (log.isDebugEnabled()) {
			log.debug("sqlLimit:" + sqlLimit + "\t limitParam:"+Arrays.toString(limitParam));
		}
		Object[] obj = null;
		//参数为空
		if (values == null || values.length == 0) {
			obj =limitParam;
		} else {
			//有其他参数
			obj = new Object[values.length + 2];
			/**
			 *  public static void arraycopy(Object src, int srcPos, Object dest, int destPos, int length)
				rc:源数组; srcPos:源数组要复制的起始位置;
				dest:目的数组; destPos:目的数组放置的起始位置; 
				length:复制的长度.
			 */
			System.arraycopy(values, 0, obj, 0, values.length);
			obj[obj.length - 2] = limitParam[0];
			obj[obj.length - 1] = limitParam[1];
		}

		ResultSetExtractor<DataList> rse = new ResultSetExtractor<DataList>() {
			public DataList extractData(ResultSet rs) throws SQLException, DataAccessException {
				return new JdbcPageDataList(rs);
			}
		};
		//jdbc查询产生的resultset会首先根据上面的extractData方法处理，产生一个JdbcPageDataList。
		DataList dataList = jdbcTemplate.query(sqlLimit, obj, rse);

		return new JdbcPage(startIndex, totalCount, pageSize, dataList);

	}

	String getDatabaseProductName(JdbcTemplate jdbcTemplate) {
		String str = databaseProduct.get(jdbcTemplate);
		if(StringUtils.isNotBlank(str)){
			return str;
		}
		DataSource ds = jdbcTemplate.getDataSource();
		Connection connection = DataSourceUtils.getConnection(ds);
		String ret = "";
		try {
			ret = connection.getMetaData().getDatabaseProductName();
		} catch (SQLException e) {
			e.printStackTrace();
			log.error("不能获得jdbc DatabaseProductName");
			DataSourceUtils.releaseConnection(connection, ds);
		} finally {
			DataSourceUtils.releaseConnection(connection, ds);
		}
		if(databaseProduct.size()>100){
			databaseProduct.clear();
		}
		databaseProduct.put(jdbcTemplate, ret);
		return ret;
	}

	int getStartOfPage(int pageNo, int pageSize) {
		return (pageNo - 1) * pageSize;
	}

	/**
	 * orderby 子句，
	 * 
	 */
	private String removeOrders(String sql) {
		Assert.hasText(sql);
		Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*",
				Pattern.CASE_INSENSITIVE);
		Matcher m = p.matcher(sql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

	@Override
	public <T> PojoPage<T> pageQuery(Class<T> requireType, String sql, int pageNo, int pageSize, Object... values) {
		String tSql = new StringBuilder("select count(1) from (").append(removeOrders(sql)).append(") a").toString();
		return pageQuery(requireType, jdbcPageDaoJdbcTemplate, sql, tSql, pageNo, pageSize, values);
	}

	@Override
	public <T> PojoPage<T> pageQuery(Class<T> requireType, String sql, String tSql, int pageNo, int pageSize,
			Object... values) {
		return pageQuery(requireType, jdbcPageDaoJdbcTemplate, sql, tSql, pageNo, pageSize, values);
	}

	@Override
	public <T> PojoPage<T> pageQuery(Class<T> requireType, DataSource ds, String sql, int pageNo, int pageSize,
			Object... values) {
		String tSql = new StringBuilder("select count(1) from (").append(removeOrders(sql)).append(") a").toString();
		JdbcTemplate jdbcTemplate = getJdbcTemplateForDS(ds);
		return pageQuery(requireType, jdbcTemplate, sql, tSql, pageNo, pageSize, values);
	}

	@Override
	public <T> PojoPage<T> pageQuery(Class<T> requireType, DataSource ds, String sql, String tSql, int pageNo,
			int pageSize, Object... values) {
		JdbcTemplate jdbcTemplate = getJdbcTemplateForDS(ds);
		return pageQuery(requireType, jdbcTemplate, sql, tSql, pageNo, pageSize, values);
	}
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public <T> PojoPage<T> pageQuery(Class<T> requireType,JdbcTemplate jdbcTemplate, final String sql, final String tSql, int pageNo,
			final int pageSize, final Object... values) {

		chechQueryParam(jdbcTemplate, sql, tSql, pageNo, pageSize, values);

		int totalCount = jdbcTemplate.queryForInt(tSql, values);
		if (totalCount <= 0)
			return new PojoPage<T>();
		final int startIndex = getStartOfPage(pageNo, pageSize);
		Dialect dialect = DialectFactory.getDialect(jdbcTemplate.getDataSource());
		String sqlLimit = dialect.getLimitString(sql, true);
		Object[] obj = buildLimitParam(pageNo, pageSize, dialect, sqlLimit, values);
	
		List query = jdbcTemplate.query(sqlLimit, obj, new ReflectionRowMapper(requireType));
		return new PojoPage(startIndex, totalCount, pageSize, query);
	}

	private Object[] buildLimitParam(int pageNo, final int pageSize, Dialect dialect, String sqlLimit,
			final Object... values) {
		Object[] limitParam = dialect.getLimitParam(pageNo,pageSize, true);
		if (log.isDebugEnabled()) {
			log.debug("sqlLimit:" + sqlLimit + "\t limitParam:"+Arrays.toString(limitParam));
		}
		Object[] obj = null;
		if (values == null || values.length == 0) {
			obj =limitParam;
		} else {
			obj = new Object[values.length + 2];
			System.arraycopy(values, 0, obj, 0, values.length);
			obj[obj.length - 2] = limitParam[0];
			obj[obj.length - 1] = limitParam[1];
		}
		return obj;
	}

	private void chechQueryParam(JdbcTemplate jdbcTemplate, final String sql, final String tSql, int pageNo,
			final int pageSize, final Object... values) {
		if (log.isDebugEnabled()) {
			log.debug("sql: " + sql);
			log.debug("tSql : " + tSql);
			log.debug("pageNo : " + pageNo);
			log.debug("pageSize : " + pageSize);
			log.debug("values :" + Arrays.toString(values));
		}
		Assert.notNull(jdbcTemplate, "需要有连接池");
		Assert.hasText(sql);
		Assert.hasText(tSql);
		Assert.isTrue(pageNo >= 1, "pageNo 应该从1开始");
		Assert.isTrue(pageSize >= 1, "pageSize 应该大于等于1");
	}
	//============================================================================================getter and setter 

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		jdbcPageDaoJdbcTemplate = new JdbcTemplate(dataSource);
		jdbcPageDaoJdbcTemplate.setMaxRows(DEFAULT_MAX_ROWS);
	}

	public int getMaxRows() {
		return maxRows;
	}

	public void setMaxRows(int maxRows) {
		this.maxRows = maxRows;
	}

	public static void main(String[] args) {
//		String sql="select * from table where 1=2 and 3=4 group by abc having ddd ORDER by des";
//		System.out.println(removeOrders(sql));
	}
	

}
